package mysql

import (
	"database/sql"
	"fmt"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func InitMysqlDB() (err error) {
	dsn := "root:root@tcp(192.168.56.101:3308)/go_db?charset=utf8mb4&parseTime=True"
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	//fmt.Printf("db: %v\n", db)
	// 最大连接时长
	db.SetConnMaxLifetime(time.Minute * 3)
	// 最大连接数
	db.SetMaxOpenConns(10)
	// 最大空闲连接数
	db.SetMaxIdleConns(10)
	// 与数据库连接
	err2 := db.Ping()
	if err2 != nil {
		return err2
	}
	return nil
}

type User struct {
	id                 int
	username, password string
}

func QueryOneRow(id int) {
	sql := "select * from user_tbl where id = ?"
	var user User
	err := db.QueryRow(sql, id).Scan(&user.id, &user.username, &user.password)
	if err != nil {
		fmt.Printf("err: %v\n", err)
		return
	}
	fmt.Printf("user: %v\n", user)
}

func QueryManyRow(id int) {
	sql := "select * from user_tbl where  id > ?"
	rows, err := db.Query(sql, id)
	if err != nil {
		fmt.Printf("err: %v\n", err)
		return
	}
	// 释放当前对象持有的数据库连接
	defer rows.Close()
	for rows.Next() {
		var user User
		err2 := rows.Scan(&user.id, &user.username, &user.password)
		if err2 != nil {
			fmt.Printf("err2: %v\n", err2)
			return
		}
		fmt.Printf("user: %v\n", user)
	}
}

func InsertData(name, pwd string) int64 {
	sql := "insert into user_tbl(username,password) values (?, ?)"
	ret, err := db.Exec(sql, name, pwd)
	if err != nil {
		fmt.Printf("err: %v\n", err)
		return 0
	}
	id, err2 := ret.LastInsertId()
	if err2 != nil {
		fmt.Printf("err2: %v\n", err2)
		return 0
	}
	return id
}

func DeleteData(name string) {
	sql := "delete from user_tbl where username = ?"
	ret, err := db.Exec(sql, name)
	if err != nil {
		fmt.Printf("err: %v\n", err)
		return
	}
	i, err2 := ret.RowsAffected()
	if err2 != nil {
		fmt.Printf("err2: %v\n", err2)
		return
	}
	fmt.Printf("受影响行数: %v\n", i)
}

func UpdateData(name, pwd string, id int) {
	sql := "update user_tbl set username=?,password=? where id = ?"
	ret, err := db.Exec(sql, name, pwd, id)
	if err != nil {
		fmt.Printf("err: %v\n", err)
		return
	}
	i, err2 := ret.RowsAffected()
	if err2 != nil {
		fmt.Printf("err2: %v\n", err2)
		return
	}
	fmt.Printf("受影响的行: %v\n", i)
}
